﻿/******************************************************   
创建者: 梁家健
创建时间: 2017-03-22
修改:
修改时间: 
功能: 单店类别金额占比
 ******************************************************/

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using com.nm.ApiServer;
using com.nm.ApiServer.Attributes;
using com.nm.Attributes;
using com.nm.library;
using george.Data;
using NHibernateVertica;
using PlanAPI.model;
using com.nm.library.Helper;

namespace PlanAPI
{
    [Description("单店类别金额占比")]
    public class PLShopKindRateHandler : BaseHandler
    {
        public VerticaDataHelper Helper { get; set; } = new VerticaDataHelper();

        /// Writer：梁家健
        /// Create Date：2017-03-22
        [MustLogin]
        [Description("单店类别金额占比")]
        [ReturnType(typeof(PaperData<IGrouping<string, PLShopKindRateModel.ListModel>>))]
        public ApiResult GetPLShopKindRateList([ParamDescription("类别")] string KindID = "",
            [ParamDescription("商品区域")] string ParentAreaId = "",
            [ParamDescription("季节")] string SeasonName = "", [ParamDescription("店铺")] string ShopIDs = "",
            [ParamDescription("页码")] int Page = 1, [ParamDescription("页数")] int Size = 10)
        {
            try
            {
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                var set = new PLPlanYMSetHandler().ReturnPLPlanYMSet();
                if (set == null) return Error("没有要修改企划的月份");
                var shopList = string.IsNullOrEmpty(ShopIDs)
                    ? ""
                    : string.Join(",", ShopIDs.Split(',').Select(item => "'" + item + "'"));
                var count = ReturnPLShopKindRateCount(KindID, ParentAreaId, shopList, set.Yearv.Value, SeasonName);
                var result = PaperData<IGrouping<string, PLShopKindRateModel.ListModel>>.Create(count, Size, Page);
                var dt = ReturnPLShopKindRateList(KindID, ParentAreaId, shopList, set.Yearv.Value, SeasonName, Page, Size);
                var list = ModelConvertHelper<PLShopKindRateModel.ListModel>.ConvertToModel(dt);
                list.ToList().ForEach(d => d.Monthv = set.Monthv);
                var query = list.GroupBy(
                        d => d.Yearv + "_" + d.SeasonID + "_" + d.ParentAreaID + "_" + d.ShopID)
                    .ToList();
                result.Items = query;
                return Success(result);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        /// <summary>
        /// 返回单店类别金额占比列表
        /// </summary>
        /// <param name="KindID"></param>
        /// <param name="ParentAreaId"></param>
        /// <param name="Keys"></param>
        /// <param name="Yearv"></param>
        /// <param name="SeasonID"></param>
        /// <returns></returns>
        /// Writer：梁家健
        /// Create Date：2017-03-22
        public DataTable ReturnPLShopKindRateList(string KindID, string ParentAreaId, string ShopList, long Yearv, string SeasonName, int Page,int Size)
        {
            try
            {
                var sql = $@"SELECT  table2.* ,
        rate.KindID ,
        rate.MoveAmountRate * 100 AS MoveAmountRate ,
        rate.SupplyAmountRate * 100 AS SupplyAmountRate ,
        rate.SaleAmountRate * 100 AS SaleAmountRate ,
        rate.MSRate * 100 AS MSRate ,
        rate.StandAmountRate * 100 AS StandAmountRate ,
        rate.PtStandAmountRate * 100 AS PtStandAmountRate ,
        rate.CkPtStandAmountRate * 100 AS CkPtStandAmountRate ,
        rate.Remark ,
        kind.KindName
FROM    ( SELECT    *
          FROM      ( SELECT    rate.CompanyID ,
                                rate.ExtrSource ,
                                rate.Yearv ,
                                rate.SeasonID ,
                                rate.ParentAreaID ,
                                rate.ShopID ,
                                season.SeasonName ,
                                dict.dictName AS ParentAreaName ,
                                shop.ShopCode ,
                                shop.ShopName ,
                                ROW_NUMBER() OVER ( ORDER BY rate.Yearv DESC, rate.SeasonID DESC, rate.ParentAreaID, rate.ShopID ) AS Row
                      FROM      ngoods.ngoods_PL_ShopKindRate rate
                                INNER JOIN ( SELECT *
                                             FROM   ndw.ndw_global_dictionary
                                             WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                                    AND CompanyID = '{UserInfo.UserCompanyId}'
                                           ) dict ON rate.ParentAreaID = dict.dictValue
                                                     AND rate.ExtrSource = dict.ExtrSource
                                                     AND rate.CompanyID = dict.CompanyID
                                INNER JOIN ( SELECT *
                                             FROM   ndw.ndw_Shop
                                             WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                                    AND CompanyID = '{UserInfo.UserCompanyId}'
                                           ) shop ON rate.ShopID = shop.ShopID
                                                     AND rate.ExtrSource = shop.ExtrSource
                                                     AND rate.CompanyID = shop.CompanyID
                                INNER JOIN ( SELECT *
                                             FROM   nods.nods_erp_SD_Mat_Season
                                             WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                           ) season ON rate.SeasonID = season.SeasonID
                                                       AND rate.ExtrSource = season.ExtrSource
                      WHERE     rate.Yearv = {Yearv}
                                AND rate.ExtrSource = '{UserInfo.UserExtrSource}'
                                AND rate.CompanyID = '{UserInfo.UserCompanyId}'
                                {(string.IsNullOrEmpty(SeasonName) ? "" : $@" and season.SeasonName='{SeasonName}' ")}
                                {(string.IsNullOrEmpty(ParentAreaId) ? "" : $@" and rate.ParentAreaId = '{ParentAreaId}' ")}
                                { (string.IsNullOrEmpty(KindID) ? "" : $@" and rate.KindID='{KindID}' ")}
                                {(string.IsNullOrEmpty(ShopList) ? "" : $@" and shop.ShopID in({ShopList}) ")}
                      GROUP BY  rate.CompanyID ,
                                rate.ExtrSource ,
                                rate.Yearv ,
                                rate.SeasonID ,
                                rate.ParentAreaID ,
                                rate.ShopID ,
                                season.SeasonName ,
                                dict.dictName ,
                                shop.ShopCode ,
                                shop.ShopName
                    ) table1
          WHERE     Row BETWEEN ( {Page} - 1 ) * {Size} + 1 AND {Page}
                    * {Size}
        ) table2
        LEFT JOIN ( SELECT  rate.*
                    FROM    ngoods.ngoods_PL_ShopKindRate rate
                            INNER JOIN ( SELECT *
                                         FROM   ndw.ndw_Shop
                                         WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                                AND CompanyID = '{UserInfo.UserCompanyId}'
                                       ) shop ON rate.ShopID = shop.ShopID
                                                 AND rate.ExtrSource = shop.ExtrSource
                                                 AND rate.CompanyID = shop.CompanyID
                    WHERE   Yearv = { Yearv}
                            AND rate.ExtrSource = '{UserInfo.UserExtrSource}'
                            AND rate.CompanyID = '{UserInfo.UserCompanyId}'
                            { (string.IsNullOrEmpty(ParentAreaId) ? "" : $@" and rate.ParentAreaId='{ParentAreaId}' ")}
                            { (string.IsNullOrEmpty(KindID) ? "" : $@" and rate.KindID='{KindID}' ")}
                            {(string.IsNullOrEmpty(ShopList) ? "" : $@" and shop.ShopID in({ShopList}) ")}
                  ) rate ON table2.CompanyID = rate.CompanyID
                            AND table2.ExtrSource = rate.ExtrSource
                            AND rate.Yearv = table2.Yearv
                            AND rate.SeasonID = table2.SeasonID
                            AND rate.ParentAreaID = table2.ParentAreaID
                            AND rate.ShopID = table2.ShopID
        INNER JOIN ( SELECT *
                     FROM   nods.nods_erp_SD_Mat_Kind
                     WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                   ) kind ON rate.KindID = kind.KindID
                             AND table2.ExtrSource = kind.ExtrSource
ORDER BY rate.Yearv DESC ,
        rate.SeasonID DESC ,
        rate.ParentAreaID ,
        rate.ShopID ,
        rate.KindID";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 返回单店类别金额占比总条数
        /// </summary>
        /// <param name="KindID"></param>
        /// <param name="ParentAreaId"></param>
        /// <param name="Keys"></param>
        /// <param name="Yearv"></param>
        /// <param name="SeasonID"></param>
        /// <returns></returns>
        /// Writer：梁家健
        /// Create Date：2017-03-22
        public int ReturnPLShopKindRateCount(string KindID, string ParentAreaId, string ShopList, long Yearv, string SeasonName)
        {
            try
            {
                var sql = $@"SELECT  COUNT(1)
FROM    ( SELECT    COUNT(1)
          FROM      ngoods.ngoods_PL_ShopKindRate rate
                    INNER JOIN ( SELECT *
                                 FROM   ndw.ndw_global_dictionary
                                 WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                        AND CompanyID = '{UserInfo.UserCompanyId}'
                               ) dict ON rate.ParentAreaID = dict.dictValue
                                         AND rate.ExtrSource = dict.ExtrSource
                                         AND rate.CompanyID = dict.CompanyID
                    INNER JOIN ( SELECT *
                                 FROM   ndw.ndw_Shop
                                 WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                        AND CompanyID = '{UserInfo.UserCompanyId}'
                               ) shop ON rate.ShopID = shop.ShopID
                                         AND rate.ExtrSource = shop.ExtrSource
                                         AND rate.CompanyID = shop.CompanyID
                    INNER JOIN ( SELECT *
                                 FROM   nods.nods_erp_SD_Mat_Kind
                                 WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                               ) kind ON rate.KindID = kind.KindID
                                         AND rate.ExtrSource = kind.ExtrSource
                    INNER JOIN ( SELECT *
                                 FROM   nods.nods_erp_SD_Mat_Season
                                 WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                               ) season ON rate.SeasonID = season.SeasonID
                                           AND rate.ExtrSource = season.ExtrSource
          WHERE     rate.Yearv = {Yearv}
                    AND rate.ExtrSource = '{UserInfo.UserExtrSource}'
                    AND rate.CompanyID = '{UserInfo.UserCompanyId}'
                    {(string.IsNullOrEmpty(SeasonName) ? "" : $@" and season.SeasonName='{SeasonName}' ")}
                    {(string.IsNullOrEmpty(ParentAreaId) ? "" : $@" and rate.ParentAreaId='{ParentAreaId}' ")}
                    {(string.IsNullOrEmpty(KindID) ? "" : $@" and rate.KindID='{KindID}' ")}
                    {(string.IsNullOrEmpty(ShopList) ? "" : $@" and shop.ShopID in ({ShopList}) ")}
GROUP BY            rate.Yearv ,
                    rate.SeasonID ,
                    rate.ParentAreaID ,
                    rate.ShopID
        ) table1 ";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Convert.ToInt32(Helper.ExecuteScalar(cmd));
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// Writer：梁家健
        /// Create Date：2017-03-22
        [JsonType]
        [MustLogin]
        [Description("修改单店类别金额占比")]
        [ReturnType(typeof(int),"0-成功")]
        public ApiResult UpdatePLShopKindRate(PLShopKindRateModel.ModfiyModel param)
        {
            try
            {
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                var sql = $@"UPDATE  ngoods.ngoods_PL_ShopKindRate
SET     CkPtStandAmountRate = {{0}} ,
        CheckState = 1
WHERE   Yearv = {{1}}
        AND SeasonID = '{{2}}'
        AND ParentAreaID = '{{3}}'
        AND ShopID = '{{4}}'
        AND KindID = '{{5}}'
        AND ExtrSource = '{UserInfo.UserExtrSource}'
        AND CompanyID = '{UserInfo.UserCompanyId}'";
                if (param == null) return Error("参数对象为空");
                if (param.list != null)
                {
                    var cmdList = new List<CommandInfo>();
                    foreach (var item in param.list)
                    {
                        var cmd = new CommandInfo()
                        {
                            CommandText =
                                string.Format(sql, item.CkPtStandAmountRate/(decimal)100.0, item.Yearv, item.SeasonID, 
                                    item.ParentAreaID, item.ShopID, item.KindID),
                        };
                        cmdList.Add(cmd);
                    }
                    Helper.ExecuteSqlTran(cmdList);
                }
                return Success(0);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }
    }
}
